<?php
include_once "db.php";
include_once "checksession.php"; 
$browsers = mysql_query("select * from browser_type");
$computer = mysql_query("select * from computer_type");
$problem = mysql_query("select * from problem_type");
$os = mysql_query("select * from operating_system");
$courses = mysql_query("select * from current_courses");
$helpdeskusers = mysql_query("select * from helpdesk_users");
$resolution_notes = mysql_query("select * from resolution_notes");
$query_all_users = "false";
// Coded by Bryan Karaffa for Santa Barbara City College
// bryankaraffa@gmail.com


?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Search Helpdesk</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="meta-style.css" rel="stylesheet" type="text/css" />

<style type="text/css">
<!--
.style1 {
	color: #FFFFFF;
	font-weight: bold;
}
-->
</style>
</head>

<body>

<?php
include_once "topnav.php";

function checkdefVal($val, $test) {
 if ($val == $test) { return " selected"; }
 else { return "";}
}

?>

<FORM action="searchcalls.php" METHOD="POST">
  <input type="hidden" name="HDuserID" value="<? echo $_SESSION[ID] ?>">
  <table width="800" BORDER=1 cellpadding=5 cellspacing=0 bordercolor="#006699" background="#CCCCCC" align="center" >
    <tr align="center" valign="top">
      <td colspan="3" bordercolor="#006699" bgcolor="#006699"><font color="white"><span class="style1">Search Helpdesk Entries</span></td>
    </tr>
	  <tr valign="top">
      <td width="50%" align="right" class="normalblue"><b>Ticket #</b></td>
      <td width="50%" colspan="2" align="left"><input name="ticket_num" type="TEXT" id="ticket_num" value="<? echo($_POST["ticket_num"]); ?>" size="35"></td>
    </tr>
    <tr valign="top">
      <td width="50%" align="right" class="normalblue"><b>First Name</b></td>
      <td width="50%" colspan="2" align="left"><input name="f_name" type="TEXT" id="f_name" value="<? echo($_POST["f_name"]); ?>" size="35"></td>
    </tr>
    <tr valign="top">
      <td width="50%" align="right" class="normalblue"><b>Last Name</b></td>
      <td width="50%" colspan="2" align="left"><input name="l_name" type="TEXT" id="l_name" value="<? echo($_POST["l_name"]); ?>" size="35"></td>
    </tr>
	<tr valigh="top">
	  <td width="50%" align="right" class="normalblue"><b>K number</b></td>
      <td width="50%" colspan="2" align="left"><input name="KNum" type="TEXT" id="KNum" value="<? echo($_POST["KNum"]); ?>" size="35"></td>
    <tr valign="top">
      <td align="right" class="normalblue"><b>UserID</b></td>
      <td colspan="2" align="left"><input name="userid" type="TEXT" id="userid" value="<? echo($_POST["userid"]); ?>" size="35"></td>
    </tr>
    <tr valign="top">
      <td align="right" class="normalblue"><b>Help Desk User</b></td>
      <td colspan="2" align="left"><select name="helpdeskuserid[]"  multiple id="helpdeskuserid[]" value="" size="5">
      <option value="">Select Helpdesk User(s)...</option>
       <?php
		 while($helpdeskuserresults = mysql_fetch_array($helpdeskusers)) {
		 echo "<option value=\"".$helpdeskuserresults[HD_user_ID]."\">".$helpdeskuserresults[name]. "</option>\n";
	      }
	     ?>
       </select></td>
    </tr>
     <tr valign="top">
       <td width="50%" align="right" class="normalblue"><b>Return Email Address</b></td>
       <td width="50%" colspan="2" align="left"><input name="email" type="TEXT" id="email" value="<? echo($_POST["email"]); ?>" size="35"></td>
     </tr>
     <tr valign="top">
       <td align="right" class="normalblue"><b>Computer Type</b></td>
       <td colspan="2" align="left"><select name="Platform[]" size=5 value="" multiple>
           <option value="">Select your computer...</option>
           <?php
		 while($computerresults = mysql_fetch_array($computer)) {
		 echo "<option value=\"".$computerresults[CT_ID]."\">".$computerresults[CT_desc]. "</option>\n";
	      }
	     ?>
       </select></td>
     </tr>
     <tr valign="top">
       <td align="right" class="normalblue"><b>Browser Type</b></td>
       <td colspan="2" align="left"><select  name="browser[]" size=5 multiple id="browser[]" value="">
           <option value="">Select a Browser...</option>
           <?php
		 while($browsersresults = mysql_fetch_array($browsers)) {
		 echo "<option value=\"".$browsersresults[BT_ID]."\">".$browsersresults[BT_desc]. "</option>\n";
	      }
	     ?>
       </select></td>
     </tr>
        <tr valign="top">
       <td width="50%" align="right" class="normalblue"><b>Operating System</b></td>
       <td width="50%" colspan="2" align="left"><select  name="operating_system[]" size=5 multiple id="operating_system[]" value="">
         <option value="">Select Operating System(s)</option>
         <?php
		 while($osresults = mysql_fetch_array($os)) {
		 echo "<option value=\"".$osresults[os_id]."\">".$osresults[os_desc]. "</option>\n";
	      }
	     ?>
       </select></td>
     </tr>
	   <tr valign="top">
       <td width="50%" align="right" class="normalblue"><b>Problem or Question Category</b></td>
       <td width="50%" colspan="2" align="left"><select  name="problem_type[]" size=6 multiple id="problem_type[]" value="">
         <option value="">Select problem type(s)...</option>
         <?php
		 while($problemresults = mysql_fetch_array($problem)) {
		 echo "<option value=\"".$problemresults[PT_ID]."\">".$problemresults[PT_desc]. "</option>\n";
	      }
	     ?>
       </select></td>
     </tr>
    <tr valign="top">
      <td width="50%" align="right" class="normalblue"><b>Course/CRN #</b></td>
      <td width="50%" colspan="2" align="left"><input name="course" type="TEXT" id="course" value="<? echo($_POST["course"]); ?>" size="35" maxlength="30"></td>
    </tr>
   
   <!--<tr valign="top">
      <td align="right" class="normalblue"><b>Semester<br>
      *not working* </b></td>
      <td colspan="2" align="left" valign="top" class="normalblue"><select name="semester[]" size="3" multiple id="semester[]">
        <option value="fall" selected>Fall</option>
        <option value="spring">Spring</option>
        <option value="summer">Summer</option>
      </select>
        <select name="semesterYear[]" size="5" multiple id="semesterYear[]">
          <option value="06">2006</option>
          <option value="07">2007</option>
          <option value="08">2008</option>
          <option value="09">2009</option>
          <option value="10">2010</option>
          <option value="11" selected>2011</option>
          <option value="12">2012</option>
        </select></td>
    </tr>
    <tr valign="top">
      <td width="50%" align="right" class="normalblue"><b>Search Problem &amp; Contact Desc<br>
        *not working*
</b></td>
      <td width="50%" colspan="2" align="left"><span class="normalblue">
        <textarea name="Problem" rows=5 cols=40 wrap="soft"></textarea>
      </span></td>
    </tr><!-->
     <tr valign="top">
       <td rowspan="2" align="right" class="normalblue"><strong>Date Range :<br>
         *MONTH, DAY, AND YEAR need to be selected to search by Start or End Dates for each respectively. *
</strong></td>
       <td align="right" class="normalblue">
	      Start:</td>
       <td align="left" valign="top" class="normalblue">12am on 
         <select name="startMonth" id="startMonth">
           <option value="%%%%" selected>Month</option>
           <option value="01">January</option>
           <option value="02">Febuary</option>
           <option value="03">March</option>
           <option value="04">April</option>
           <option value="05">May</option>
           <option value="06">June</option>
           <option value="07">July</option>
           <option value="08">August</option>
           <option value="09">September</option>
           <option value="10">October</option>
           <option value="11">November</option>
           <option value="12">December</option>
                                    </select>
         <select name="startDay" id="startDay">
           <option value="%%%%" selected>Day</option>
           <option value="01">1</option>
           <option value="02">2</option>
           <option value="03">3</option>
           <option value="04">4</option>
           <option value="05">5</option>
           <option value="06">6</option>
           <option value="07">7</option>
           <option value="08">8</option>
           <option value="09">9</option>
           <option value="10">10</option>
           <option value="11">11</option>
           <option value="12">12</option>
           <option value="13">13</option>
           <option value="14">14</option>
           <option value="15">15</option>
           <option value="16">16</option>
           <option value="17">17</option>
           <option value="18">18</option>
           <option value="19">19</option>
           <option value="20">20</option>
           <option value="21">21</option>
           <option value="22">22</option>
           <option value="23">23</option>
           <option value="24">24</option>
           <option value="25">25</option>
           <option value="26">26</option>
           <option value="27">27</option>
           <option value="28">28</option>
           <option value="29">29</option>
           <option value="30">30</option>
           <option value="31">31</option>
                                             </select>
         <select name="startYear" id="startYear">
           <option value="%%%%" selected>Year</option>
           <option value="2012">2012</option>
           <option value="2011">2011</option>
           <option value="2010">2010</option>
           <option value="2009">2009</option>
           <option value="2008">2008</option>
           <option value="2007">2007</option>
                                                      </select></td>
     </tr>
     <tr valign="top">
       <td align="right" class="normalblue">End:</td>
       <td align="left" valign="top" class="normalblue">12am on 
         <select name="endMonth" id="endMonth">
           <option value="%%" selected>Month</option>
           <option value="01">January</option>
           <option value="02">Febuary</option>
           <option value="03">March</option>
           <option value="04">April</option>
           <option value="05">May</option>
           <option value="06">June</option>
           <option value="07">July</option>
           <option value="08">August</option>
           <option value="09">September</option>
           <option value="10">October</option>
           <option value="11">November</option>
           <option value="12">December</option>
                                    </select>
         <select name="endDay" id="endDay">
           <option value="%%" selected>Day</option>
           <option value="01">1</option>
           <option value="02">2</option>
           <option value="03">3</option>
           <option value="04">4</option>
           <option value="05">5</option>
           <option value="06">6</option>
           <option value="07">7</option>
           <option value="08">8</option>
           <option value="09">9</option>
           <option value="10">10</option>
           <option value="11">11</option>
           <option value="12">12</option>
           <option value="13">13</option>
           <option value="14">14</option>
           <option value="15">15</option>
           <option value="16">16</option>
           <option value="17">17</option>
           <option value="18">18</option>
           <option value="19">19</option>
           <option value="20">20</option>
           <option value="21">21</option>
           <option value="22">22</option>
           <option value="23">23</option>
           <option value="24">24</option>
           <option value="25">25</option>
           <option value="26">26</option>
           <option value="27">27</option>
           <option value="28">28</option>
           <option value="29">29</option>
           <option value="30">30</option>
           <option value="31">31</option>
                           </select>
         <select name="endYear" id="endYear">
           <option value="%%%%" selected>Year</option>
           <option value="2012">2012</option>
           <option value="2011">2011</option>
           <option value="2010">2010</option>
           <option value="2009">2009</option>
           <option value="2008">2008</option>
           <option value="2007">2007</option>
         </select></td>
     </tr>
     <tr valign="top">
       <td width="50%" align="right" class="normalblue">&nbsp;</td>
       <td width="50%" colspan="2" align="left"><span class="normalblue">
	   <input name="post" type="hidden" value="1">
       <input type=SUBMIT value="Search Helpdesk" name="SUBMIT">
       </span></td>
     </tr>
  </table>
</FORM>

<?
if ($_POST["post"] == "1") {
	echo "Search Results:<br />";
	$query = "Select * FROM Calls WHERE ";
	$qcount = 0;
	
	if ($_POST["f_name"] != NULL) {
		$query .= "`first_name`='".$_POST["f_name"]."' ";
		$qcount++;
	}
	if ($_POST["l_name"] != NULL) {
		if ($qcount > 0) {
			$query .= "AND ";
		}
		$query .= "`last_name`='".$_POST["l_name"]."' ";
		$qcount++;
	}
	if ($_POST["KNum"] != NULL) {
		if ($qcount > 0) {
			$query .= "AND ";
		}
		$query .= "`KNum`='".$_POST["KNum"]."' ";
		$qcount++;
	}
	if ($_POST["userid"] != NULL) {
		if ($qcount > 0) {
			$query .= "AND ";
		}
		$query .= "`pipeline_userid`='".$_POST["userid"]."' ";
		$qcount++;
	}
	if ($_POST["email"] != NULL) {
		if ($qcount > 0) {
			$query .= "AND ";
		}
		$query .= "`email`='".$_POST["email"]."' ";
		$qcount++;
	}
	if ($_POST["Platform"] != NULL) {
		$t = $_POST["Platform"];
		if ($qcount > 0) { $query .= "AND "; }
				
		if (is_array($t)) { 
			foreach ($t as $p){ $query .= "`ostype`='".$p."' OR "; }
			$query = substr_replace($query ,"",-3);
		}
		else { $query .= "`ostype`='".$_POST["Platform"]."' "; }
		$qcount++;
	}
	if ($_POST["browser"] != NULL) {
		$t = $_POST["browser"];
		if ($qcount > 0) { $query .= "AND "; }
				
		if (is_array($t)) { 
			foreach ($t as $p){ $query .= "`browser_type`='".$p."' OR "; }
			$query = substr_replace($query ,"",-3);
		}
		else { $query .= "`browser_type`='".$_POST["browser"]."' "; }
		$qcount++;
	}
	if ($_POST["operating_system"] != NULL) {
		$t = $_POST["operating_system"];
		if ($qcount > 0) { $query .= "AND "; }
				
		if (is_array($t)) { 
			foreach ($t as $p){ $query .= "`os_type`='".$p."' OR "; }
			$query = substr_replace($query ,"",-3);
		}
		else { $query .= "`os_type`='".$_POST["operating_system"]."' "; }
		$qcount++;
	}
	if ($_POST["problem_type"] != NULL) {
		$t = $_POST["problem_type"];
		if ($qcount > 0) { $query .= "AND "; }
				
		if (is_array($t)) { 
			foreach ($t as $p){ $query .= "`problem_type`='".$p."' OR "; }
			$query = substr_replace($query ,"",-3);
		}
		else { $query .= "`problem_type`='".$_POST["problem_type"]."' "; }
		$qcount++;
	}
	if ($_POST["helpdeskuserid"] != NULL) {
		$query_all_users = "true";
		$t = $_POST["helpdeskuserid"];
		if ($qcount > 0) { $query .= "AND "; }
				
		if (is_array($t)) { 
			foreach ($t as $p){ $query .= "`helpdesk_user`='".$p."' OR "; }
			$query = substr_replace($query ,"",-3);
		}
		else { $query .= "`helpdesk_user`='".$_POST["helpdeskuserid"]."' "; }
		$qcount++;
	}
	if ($_POST["course"] != NULL) {
		if ($qcount > 0) {
			$query .= "AND ";
		}
		$query .= "`course`='".$_POST["course"]."' ";
		$qcount++;
	}
	if ($_POST["ticket_num"] != NULL) {
		if ($qcount > 0) {
			$query .= "AND ";
		}
		$query .= "`Call_ID`='".$_POST["ticket_num"]."' ";
		$qcount++;
	}
	if (($_POST["startMonth"] != '%%') && ($_POST["startDay"] != '%%') && ($_POST["startYear"] != '%%%%')) {
	// && ($_POST["endMonth"] != NULL) && ($_POST["endDay"] != NULL) && ($_POST["endYear"] != NULL))   {
		if ($qcount > 0) {
			$query .= "AND ";
		}
		$startDate=$_POST["startYear"].'-'.$_POST["startMonth"].'-'.$_POST["startDay"];
		$query .= "`request_date` > '".$startDate."%' ";
		$qcount++;
	}
	if (($_POST["endMonth"] != '%%') && ($_POST["endDay"] != '%%') && ($_POST["endYear"] != '%%%%')) {
		if ($qcount > 0) {
			$query .= "AND ";
		}
		$endday = $_POST["endDay"];
		$endDate=$_POST["endYear"].'-'.$_POST["endMonth"].'-'.$endday.'';
		$query .= "`request_date` < '".$endDate."%' ";
		$qcount++;
	}

	//Query the DB & Format Results
	$resultcount = 0;
	if ($_POST['post']) {
		$result = mysql_query($query);
		while ($row = mysql_fetch_array($result)) {
		$resultcount++;
		//For each row selected, format it into HTML.
		if ($resultcount & 1) { $bgcolor=' bgcolor="#CCCCCC"'; }
		else { $bgcolor=" bgcolor=''"; }
		
		$results .="<tr".$bgcolor."><td>".$row['first_name']." ".$row['m_initial']." ".$row['last_name']."</td><td>".$row['email']."</td><td>".$row['pipeline_userid']."</td><td>".$row['request_date']."</td></tr>\n";
		$results .="<tr".$bgcolor."><td colspan='3'>".reverseProbtype($row['problem_type'])."</td><td><a href='calls_detail.php?callid=".$row['Call_ID']."'>View Details</a></td></tr>\n";
	}
	//Print Results
	echo "<p>Search Results:<br>";
	echo "Query: ".$query."<br>";
	echo "Results: ".$resultcount."<br></p>";
	}
		
}
?>
 <table width="650" border="=" cellpadding="2" cellspacing="2">
 <? echo($results); ?>
 </table>
 <?php 
 // print out additional users resolution notes if Help Desk User Is assigned
 if ($query_all_users == "true"){
 $qcount = 0;
 $user_resultcount = 0;
 echo "<h2> Additional Help Desk Users Resolution Notes:</h2>";
 $users = $_POST["helpdeskuserid"];
 $user_query = "SELECT * from resolution_notes WHERE ";
 if ($qcount > 0) { $user_query .= "AND "; }
 if (is_array($users)) { 
			foreach ($users as $user){ $user_query .= "`HD_user_ID`='".$user."' OR "; }
			$user_query = substr_replace($user_query ,"",-3);
		}
		else { $user_query .= "`HD_user_ID`='".$_POST["helpdeskuserid"]."' "; }
		$qcount++;
if (($_POST["startMonth"] != '%%') && ($_POST["startDay"] != '%%') && ($_POST["startYear"] != '%%%%')) {
	// && ($_POST["endMonth"] != NULL) && ($_POST["endDay"] != NULL) && ($_POST["endYear"] != NULL))   {
		if ($qcount > 0) {
			$user_query .= "AND ";
		}
		$startDate=$_POST["startYear"].'-'.$_POST["startMonth"].'-'.$_POST["startDay"];
		$user_query .= "`note_date` > '".$startDate."%' ";
		$qcount++;
	}
	if (($_POST["endMonth"] != '%%') && ($_POST["endDay"] != '%%') && ($_POST["endYear"] != '%%%%')) {
		if ($qcount > 0) {
			$user_query .= "AND ";
		}
		$endday = $_POST["endDay"];
		$endDate=$_POST["endYear"].'-'.$_POST["endMonth"].'-'.$endday.'';
		$user_query .= "`note_date` < '".$endDate."%' ";
		$qcount++;
	}
 $user_results = mysql_query($user_query);
 while ($row = mysql_fetch_array($user_results)) {
		$user_resultcount++;
		if ($user_resultcount & 1) { $bgcolor=' bgcolor="#CCCCCC"'; }
		else { $bgcolor=" bgcolor=''"; }
		
		$user_result .="<tr".$bgcolor."><td>".$row['note_ID']." </td><td>".$row['contact_type']."</td><td>".$row['HD_user_ID']."</td><td>".$row['note_date']."</td></tr>\n";
		$user_result .="<tr".$bgcolor."><td colspan='3'>".$row['note_desc']."</td><td><a href='calls_detail.php?callid=".$row['call_ID']."'>View Details</a></td></tr>\n";
	}
	//Print Results
	echo "<p>Search Results:<br>";
	echo "Query: ".$user_query."<br>";
	echo "Results: ".$user_resultcount."<br></p>";
	}
	
		?>
 <table width="650" border="=" cellpadding="2" cellspacing="2">
 <?php echo($user_result); ?>
 </table>
		
</body>
</html>
